package com.jjmmbb.kebiao.db;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.jjmmbb.kebiao.pojo.Course;

/**
 * @author yizhe
 * @date 2014-3-1
 */
public class CourseDao extends DBHelper {

	public CourseDao(Context context) {
		super(context);
	}

	public static String getTableName() {
		return "Course";
	}

	@Override
	String setTableName() {
		return "Course";
	}

	// pojo的整数都需要使用Long类型 或者Integer类型 建议使用Long
	// 对象-->表字段 映射
	public void iniContentValues(Course pojo) {
		contentValues = new ContentValues();
		contentValues.put("id", pojo.id);
		contentValues.put("time", pojo.time);
		contentValues.put("name", pojo.name);
		contentValues.put("x", pojo.x);
		contentValues.put("y", pojo.y);
		contentValues.put("teacher", pojo.teacher);
		contentValues.put("meno", pojo.meno);
	}

	// cursor-->对象 映射
	public Course setBaseItem(Cursor cursor) {
		Course pojo = new Course();
		pojo.id = cursor.getInt(cursor.getColumnIndex("id"));
		pojo.time = cursor.getLong(cursor.getColumnIndex("time"));
		pojo.name = cursor.getString(cursor.getColumnIndex("name"));
		pojo.x = cursor.getInt(cursor.getColumnIndex("x"));
		pojo.y = cursor.getInt(cursor.getColumnIndex("y"));
		pojo.teacher = cursor.getString(cursor.getColumnIndex("teacher"));
		pojo.meno = cursor.getString(cursor.getColumnIndex("meno"));

		return pojo;
	}

	// --------------------自定义方法--------------------------------//
	/**
	 * 根据sql获取list
	 * 
	 * @param sql
	 * @return
	 */
	public ArrayList<Course> getListByY(Integer Y) {
		String sql = "select * from " + tableName + " where y=" + Y
				+ " order by x";
		return getListAsSQL(sql, null);
	}

	/**
	 * 获取某科目下面的全部Grade
	 */
	public ArrayList<Course> getList4ReviewMode(int SubjectID) {
		String sql1 = "Select * From %s Where ShowMode=1 And SubjectID = %s";
		String sql = String.format(sql1, tableName, SubjectID);
		ArrayList<Course> itemList = getListAsSQL(sql, null);
		return itemList;
	}

	/**
	 * 获取某题库下面的全部题目,<br>
	 * 表格格式: "title", "content",
	 */
	public ArrayList<Course> getReviewList() {
		String sql1 = "Select * From %s Where EverRead=1 And ShowMode=1 order by SubjectID, id ";
		String sql = String.format(sql1, tableName);
		return getListAsSQL(sql, null);
	}

	/**
	 * 清空
	 */
	public int clearBySubjectID(int SubjectID) {
		String sql1 = " SubjectID = %s";
		String sql = String.format(sql1, SubjectID);
		return delete(sql, null);
	}

	/**
	 * 更新复习次数,复习次数增加一次
	 * 
	 * @param gradeID
	 */
	public void updateReviewTimes(int gradeID) {
		String sql1 = "Update %s set ReviewTimes=ReviewTimes+1 Where id = %s";
		String sql = String.format(sql1, tableName, gradeID);
		execSQL(sql);
	}

	/**
	 * 按时间倒序查询出全部数据
	 */
	public ArrayList<Course> getListOrder() {
		String sql = "select * from " + tableName + " order by time desc";
		ArrayList<Course> itemList = getListAsSQL(sql, null);
		return itemList;
	}

	// --------------------基础方法---------------------------------//

	/**
	 * 根据sql获取list<br>
	 * 例如:<br>
	 * sql = "Select * from myTable where name=?" <br>
	 * whereParmas = new String[] {"baozi"}
	 * 
	 * @param sql
	 * @param whereParmas
	 *            where 子句里面?处替换的参数; 没有传null
	 * @return
	 */
	public ArrayList<Course> getListAsSQL(String sql, String[] whereParmas) {
		System.out.println("getListAsSQL_sql:" + sql);
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, whereParmas);
		ArrayList<Course> itemList = new ArrayList<Course>();
		for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
			itemList.add(setBaseItem(cursor));
		}
		cursor.close();
		db.close();
		return itemList;
	}

	/**
	 * 根据ID获取一条记录
	 */
	public Course getById(int id) {
		String sql = "select * from " + tableName + " where id=" + id;
		return getOneAsSQL(sql);
	}

	/**
	 * 返回结果中的提一条记录
	 */
	public Course getOneAsSQL(String sql) {
		System.out.println(sql);
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] {});
		try {
			if (null != cursor && cursor.getCount() > 0) {
				cursor.moveToFirst();
				return setBaseItem(cursor);
			}
		} finally {
			cursor.close();
			db.close();
		}
		return null;
	}

	/**
	 * 保存对象到数据库,自动判断插入还是更像
	 * 
	 * @return 返回更新的记录数,或者 插入数据的id,如果返回值<=0表示失败
	 */
	public long save(Course pojo) {
		if (null == pojo.time || 0 == pojo.time) {
			pojo.time = new Date().getTime();
		}
		Long idOrEffectRows = 0l;
		if (null == pojo.id || pojo.id < 1) {
			idOrEffectRows = insert(pojo);
		} else {
			idOrEffectRows = (long) update(pojo);
		}
		return idOrEffectRows;
	}

	/**
	 * 添加数据,自动插入id
	 * 
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(Course pojo) {
		SQLiteDatabase db = getWritableDatabase();// 获取可写SQLiteDatabase对象
		iniContentValues(pojo); // ContentValues类似map，存入的是键值对
		long result = db.insert(tableName, null, contentValues);
		if (result != -1) {
			pojo.id = (int) result;
		}
		db.close();
		return result;
	}

	/**
	 * 根据ID更新记录的，跟插入的很像
	 * 
	 * @return the number of rows affected
	 * 
	 */
	public int update(Course pojo) {
		SQLiteDatabase db = getWritableDatabase();
		iniContentValues(pojo); // 初始化键值对
		int result = db.update(tableName, contentValues, "id=?",
				new String[] { pojo.id + "" });
		db.close();
		return result;
	}

}
